from sqlalchemy import create_engine, or_
from sqlalchemy.orm import scoped_session, sessionmaker, session
from SQLAlchemy import createtables
import random as r
from SQLAlchemy import choicedemo
from SQLAlchemy import constants

IP = constants.IP
# db_connect_string='mysql://liu:liu@121.41.203.238:3306/bishe?charset=utf8'
db_connect_string = 'mysql://liu:liu@' + IP + ':3306/bishe?charset=utf8'
engine = create_engine(db_connect_string)
SessionType = scoped_session(sessionmaker(bind=engine, expire_on_commit=False))


def GetSession():
    return SessionType()


from contextlib import contextmanager


@contextmanager
def session_scope():
    session = GetSession()
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()


class Insert():
    def Admin(self, id, name, gender, telephone):
        with session_scope() as session:
            account = createtables.Admin(admin_id=id, name=name, gender=gender, telephone=telephone)
            session.add(account)

    def LogIn(self, id, password=None):
        with session_scope() as session:
            account = createtables.LogIn(login_id=id, password=password)
            session.add(account)

    def Teacher(self, id, name, gender, telephone, course_id):
        with session_scope() as session:
            account = createtables.Teacher(teacher_id=id, name=name, gender=gender, telephone=telephone,
                                           course_id=course_id)
            session.add(account)

    def ChoiseCourse(self, teacher_id, student_id):
        with session_scope() as session:
            account = createtables.ChoiseCourse(teacher_id=teacher_id, student_id=student_id)
            session.add(account)

    def Student(self, id, name, gender, telephone, class_id):
        with session_scope() as session:
            account = createtables.Student(student_id=id, name=name, gender=gender, telephone=telephone,
                                           class_id=class_id)
            session.add(account)

    def Course(self, id, course):
        with session_scope() as session:
            account = createtables.Course(course_id=id, course=course)
            session.add(account)

    def Mark(self, mark_id, student_id, course_id, mark):
        with session_scope() as session:
            account = createtables.Mark(mark_id=mark_id, student_id=student_id, course_id=course_id, mark=mark)
            session.add(account)

    def TimeTable(self, time_id, week, time):
        with session_scope() as session:
            account = createtables.TimeTable(time_id=time_id, week=week, time=time)
            session.add(account)

    def SchoolTimeTable(self, school_time_table_id, time_id, classroom_id, class_id, teacher_id):
        with session_scope() as session:
            account = createtables.SchoolTimeTable(school_time_table_id=school_time_table_id,
                                                   time_id=time_id, classroom_id=classroom_id, class_id=class_id,
                                                   teacher_id=teacher_id)
            session.add(account)

    def ClassTeacher(self, teacher_id, class_id):
        with session_scope() as session:
            account = createtables.ClassTeacher(teacher_id=teacher_id, class_id=class_id)
            session.add(account)

    def Assess(self, assess_id, teacher_id, student_id, content):
        with session_scope() as session:
            account = createtables.Assess(assess_id=assess_id, teacher_id=teacher_id, student_id=student_id,
                                          content=content)
            session.add(account)

    def Design(self, design_id, teacher_id, student_id, name):
        with session_scope() as session:
            account = createtables.Design(design_id=design_id, teacher_id=teacher_id, student_id=student_id, name=name)
            session.add(account)

    def Class(self, class_id, major, grade, clas):
        with session_scope() as session:
            account = createtables.Class(class_id=class_id, major=major, grade=grade, clas=clas)
            session.add(account)

    def Classroom(self, classroom_id, name):
        with session_scope() as session:
            account = createtables.Classroom(classroom_id=classroom_id, name=name)
            session.add(account)


class Get():
    def Admin(self, admin_id=None, name=None, gender=None, telephone=None):
        with session_scope() as session:
            return session.query(createtables.Admin).filter(
                or_(createtables.Admin.admin_id == admin_id, createtables.Admin.name == name,
                    createtables.Admin.gender == gender, createtables.Admin.telephone == telephone)).first()

    def LogIn(self, login_id=None, password=None):
        with session_scope() as session:
            return session.query(createtables.LogIn).filter(
                or_(createtables.LogIn.login_id == login_id, createtables.LogIn.password == password)).first()

    def Teacher(self, teacher_id=None, name=None, gender=None, telephone=None, course_id=None):
        with session_scope() as session:
            return session.query(createtables.Teacher).filter(
                or_(createtables.Teacher.teacher_id == teacher_id, createtables.Teacher.name == name,
                    createtables.Teacher.gender == gender, createtables.Teacher.telephone == telephone,
                    createtables.Teacher.course_id == course_id)).first()

    def TeacherAll(self):
        with session_scope() as session:
            return session.query(createtables.Teacher).filter().all()

    def ChoiseCourse(self, teacher_id=None, student_id=None):
        with session_scope() as session:
            return session.query(createtables.ChoiseCourse).filter(
                or_(createtables.ChoiseCourse.teacher_id == teacher_id,
                    createtables.ChoiseCourse.student_id == student_id)).first()

    def ChoiseCourseAll(self):
        with session_scope() as session:
            return session.query(createtables.ChoiseCourse).filter().all()

    def Student(self, student_id=None, name=None, gender=None, telephone=None, class_id=None):
        with session_scope() as session:
            return session.query(createtables.Student).filter(
                or_(createtables.Student.student_id == student_id, createtables.Student.name == name
                    , createtables.Student.gender == gender, createtables.Student.telephone == telephone
                    , createtables.Student.class_id == class_id)).first()

    def StudentMessageAll(self, student_id=None, name=None, gender=None, telephone=None, class_id=None):
        with session_scope() as session:
            return session.query(createtables.Student).filter(
                or_(createtables.Student.student_id == student_id, createtables.Student.name == name
                    , createtables.Student.gender == gender, createtables.Student.telephone == telephone
                    , createtables.Student.class_id == class_id)).all()
    def StudentAll(self, student_id=None, name=None, gender=None, telephone=None, class_id=None):
        with session_scope() as session:
            return session.query(createtables.Student).filter().all()

    def Course(self, course_id=None, course=None):
        with session_scope() as session:
            return session.query(createtables.Course).filter(
                or_(createtables.Course.course_id == course_id, createtables.Course.course == course)).first()

    # 成绩
    def Mark(self, mark_id=None, student_id=None, course_id=None, mark=None):
        with session_scope() as session:
            return session.query(createtables.Mark).filter(
                or_(createtables.Mark.mark_id == mark_id, createtables.Mark.student_id == student_id,
                    createtables.Mark.course_id == course_id
                    , createtables.Mark.mark == mark)).first()

    def TimeTable(self, time_id=None, week=None, time=None):
        with session_scope() as session:
            return session.query(createtables.TimeTable).filter(
                or_(createtables.TimeTable.time_id == time_id, createtables.TimeTable.week == week
                    , createtables.TimeTable.time == time)).first()

    def SchoolTimeTable(self,school_time_table_id=None, time_id=None, classroom_id=None, class_id=None, teacher_id=None):
        with session_scope() as session:
            return session.query(createtables.SchoolTimeTable).filter(
                or_(createtables.SchoolTimeTable.school_time_table_id == school_time_table_id,
                    createtables.SchoolTimeTable.time_id == time_id, createtables.SchoolTimeTable.class_id == class_id
                    , createtables.SchoolTimeTable.classroom_id == classroom_id,
                    createtables.SchoolTimeTable.teacher_id == teacher_id)).all()

    def SchoolTimeTableAll(self,school_time_table_id=None, time_id=None, classroom_id=None, class_id=None, teacher_id=None):
        with session_scope() as session:
            return session.query(createtables.SchoolTimeTable).filter(
                or_(createtables.SchoolTimeTable.school_time_table_id == school_time_table_id,
                    createtables.SchoolTimeTable.time_id == time_id, createtables.SchoolTimeTable.class_id == class_id
                    , createtables.SchoolTimeTable.classroom_id == classroom_id,
                    createtables.SchoolTimeTable.teacher_id == teacher_id)).all()

    def ClassTeacher(self, teacher_id=None, class_id=None):
        with session_scope() as session:
            return session.query(createtables.ClassTeacher).filter(
                or_(createtables.ClassTeacher.teacher_id == teacher_id,
                    createtables.ClassTeacher.class_id == class_id)).first()

    def ClassTeacherAll(self, teacher_id=None, class_id=None):
        with session_scope() as session:
            return session.query(createtables.ClassTeacher).filter(
                or_(createtables.ClassTeacher.teacher_id == teacher_id,
                    createtables.ClassTeacher.class_id == class_id)).all()

    def Assess(self, assess_id=None, teacher_id=None, student_id=None, content=None):
        with session_scope() as session:
            return session.query(createtables.Assess).filter(
                or_(createtables.Assess.assess_id == assess_id, createtables.Assess.teacher_id == teacher_id,
                    createtables.Assess.student_id == student_id
                    , createtables.Assess.content == content)).first()

    def AssessAll(self, assess_id=None, teacher_id=None, student_id=None, content=None):
        with session_scope() as session:
            return session.query(createtables.Assess).filter(
                or_(createtables.Assess.assess_id == assess_id, createtables.Assess.teacher_id == teacher_id,
                    createtables.Assess.student_id == student_id
                    , createtables.Assess.content == content)).all()

    def Design(self, design_id=None, teacher_id=None, student_id=None, name=None):
        with session_scope() as session:
            return session.query(createtables.Design).filter(
                or_(createtables.Design.design_id == design_id, createtables.Design.teacher_id == teacher_id,
                    createtables.Design.student_id == student_id
                    , createtables.Design.name == name)).first()

    def DesignMessageAll(self, design_id=None, teacher_id=None, student_id=None, name=None):
        with session_scope() as session:
            return session.query(createtables.Design).filter(
                or_(createtables.Design.design_id == design_id, createtables.Design.teacher_id == teacher_id,
                    createtables.Design.student_id == student_id
                    , createtables.Design.name == name)).all()

    def DesignAll(self):
        with session_scope() as session:
            return session.query(createtables.Design).filter().all()

    def Class(self, class_id=None, major=None, grade=None, clas=None):
        with session_scope() as session:
            return session.query(createtables.Class).filter(
                or_(createtables.Class.class_id == class_id, createtables.Class.major == major
                    , createtables.Class.grade == grade, createtables.Class.clas == clas)).first()

    def Classroom(self, classroom_id=None, name=None):
        with session_scope() as session:
            return session.query(createtables.Classroom).filter(
                or_(createtables.Classroom.classroom_id == classroom_id, createtables.Classroom.name == name)).first()


class Delete():
    def Admin(self, admin_id=None, name=None, gender=None, telephone=None):
        with session_scope() as session:
            account = Get().Admin(admin_id, name, gender, telephone)
            if account:
                session.delete(account)

    def LogIn(self, login_id=None, password=None):
        with session_scope() as session:
            account = Get().LogIn(login_id, password)
            if account:
                session.delete(account)

    def Teacher(self, teacher_id=None, name=None, gender=None, telephone=None, course_id=None):
        with session_scope() as session:
            account = Get().Teacher(teacher_id, name, gender, telephone, course_id)
            if account:
                session.delete(account)

    def ChoiseCourse(self, teacher_id=None, student_id=None):
        with session_scope() as session:
            account = Get().ChoiseCourse(teacher_id, student_id)
            if account:
                session.delete(account)

    def Student(self, student_id=None, name=None, gender=None, telephone=None, class_id=None):
        with session_scope() as session:
            account = Get().Student(student_id, name, gender, telephone, class_id)
            if account:
                session.delete(account)

    def Course(self, course_id=None, course=None):
        with session_scope() as session:
            account = Get().Course(course_id, course)
            if account:
                session.delete(account)

    def Mark(self, mark_id=None, student_id=None, course_id=None, mark=None):
        with session_scope() as session:
            account = Get().Mark(mark_id, student_id, course_id, mark)
            if account:
                session.delete(account)

    def TimeTable(self, time_id=None, week=None, time=None):
        with session_scope() as session:
            account = Get().TimeTable(time_id, week, time)
            if account:
                session.delete(account)

    # def SchoolTimeTable(self,school_time_table_id=None,time_id=None, classroom_id=None,class_id=None, teacher_id=None):
    #     with session_scope() as session:
    #         account=Get().SchoolTimeTable(school_time_table_id,time_id, classroom_id,class_id,teacher_id)
    #         if account:
    #             session.delete(account)

    def ClassTeacher(self, teacher_id=None, class_id=None):
        with session_scope() as session:
            account = Get().ClassTeacher(teacher_id, class_id)
            if account:
                session.delete(account)

    def Assess(self, assess_id=None, teacher_id=None, student_id=None, content=None):
        with session_scope() as session:
            account = Get().Assess(assess_id, teacher_id, student_id, content)
            if account:
                session.delete(account)

    def Design(self, design_id=None, teacher_id=None, student_id=None, name=None):
        with session_scope() as session:
            account = Get().Design(design_id, teacher_id, student_id, name)
            if account:
                session.delete(account)

    def Class(self, class_id=None, major=None, grade=None, clas=None):
        with session_scope() as session:
            account = Get().Class(class_id, major, grade, clas)
            if account:
                session.delete(account)

    def Classroom(self, classroom_id=None, name=None):
        with session_scope() as session:
            account = Get().Classroom(classroom_id, name)
            if account:
                session.delete(account)


class Update():
    def Admin(self, admin_id=None, name=None, gender=None, telephone=None):
        with session_scope() as session:
            account = session.query(createtables.Admin).filter(createtables.Admin.admin_id == admin_id).first()
            if not account: return
            account.admin_id = admin_id
            account.name = name
            account.gender = gender
            account.telephone = telephone

    def LogIn(self, login_id=None, password=None):
        with session_scope() as session:
            account = session.query(createtables.LogIn).filter(
                createtables.LogIn.login_id == login_id).first()
            if not account: return
            account.login_id = login_id
            account.password = password

    def Teacher(self, teacher_id=None, name=None, gender=None, telephone=None, course_id=None):
        with session_scope() as session:
            account = session.query(createtables.Teacher).filter(
                createtables.Teacher.teacher_id == teacher_id).first()
            if not account: return
            account.teacher_id = teacher_id
            account.name = name
            account.gender = gender
            account.telephone = telephone
            account.course_id = course_id

    def ChoiseCourse(self, teacher_id=None, student_id=None):
        with session_scope() as session:
            account = session.query(createtables.ChoiseCourse).filter(
                createtables.ChoiseCourse.teacher_id == teacher_id).first()
            if not account: return
            account.teacher_id = teacher_id
            account.student_id = student_id

    def Student(student_id=None, name=None, gender=None, telephone=None, class_id=None):
        with session_scope() as session:
            account = session.query(createtables.Student).filter(
                createtables.Student.student_id == student_id).first()
            if not account: return
            account.student_id = student_id
            account.name = name
            account.gender = gender
            account.telephone = telephone
            account.class_id = class_id

    def Course(self, course_id=None, course=None):
        with session_scope() as session:
            account = session.query(createtables.Course).filter(
                createtables.Course.course_id == course_id).first()
            if not account: return
            account.course_id = course_id
            account.course = course

    def Mark(self, mark_id=None, student_id=None, course_id=None, mark=None):
        with session_scope() as session:
            account = session.query(createtables.Mark).filter(
                createtables.Mark.mark_id == mark_id).first()
            if not account: return
            account.student_id = student_id
            account.course_id = course_id
            account.mark = mark

    def TimeTable(self, time_id=None, week=None, time=None):
        with session_scope() as session:
            account = session.query(createtables.TimeTable).filter(
                createtables.TimeTable.time_id == time_id).first()
            if not account: return
            account.time_id = time_id
            account.week = week
            account.time = time

    def SchoolTimeTable(self, school_time_table_id=None, time_id=None, classroom_id=None, class_id=None,
                        teacher_id=None):
        with session_scope() as session:
            account = session.query(createtables.SchoolTimeTable).filter(
                createtables.SchoolTimeTable.school_time_table_id == school_time_table_id).first()
            if not account: return
            account.time = time_id
            account.classroom_id = classroom_id
            account.class_id = class_id
            account.teacher_id = teacher_id

    def ClassTeacher(self, teacher_id=None, class_id=None):
        with session_scope() as session:
            account = session.query(createtables.ClassTeacher).filter(
                createtables.ClassTeacher.teacher_id == teacher_id).first()
            if not account: return
            account.teacher_id = teacher_id
            account.class_id = class_id

    def Assess(self, assess_id=None, teacher_id=None, student_id=None, content=None):
        with session_scope() as session:
            account = session.query(createtables.Assess).filter(
                createtables.Assess.assess_id == assess_id).first()
            if not account: return
            account.teacher_id = teacher_id
            account.student_id = student_id
            account.content = content

    def Design(self, design_id=None, teacher_id=None, student_id=None, name=None):
        with session_scope() as session:
            account = session.query(createtables.Design).filter(
                createtables.Design.design_id == design_id).first()
            if not account: return
            account.teacher_id = teacher_id
            account.student_id = student_id
            account.name = name

    def Class(self, class_id=None, major=None, grade=None, clas=None):
        with session_scope() as session:
            account = session.query(createtables.Class).filter(
                createtables.Class.class_id == class_id).first()
            if not account: return
            account.class_id = class_id
            account.major = major
            account.grade = grade
            account.clas = clas

    def Classroom(self, classroom_id=None, name=None):
        with session_scope() as session:
            account = session.query(createtables.Class).filter(
                createtables.Classroom.classroom_id == classroom_id).first()
            if not account: return
            account.name = name
# Insert().Admin(1,choicedemo.Choice_name(),choicedemo.Choice_gender(),choicedemo.Choice_telephone())
# print(type(choicedemo.Choice_gender()),len(choicedemo.Choice_gender()))
# Insert().Admin(id=1,name=choicedemo.Choice_name(),gender=choicedemo.Choice_gender(),telephone=choicedemo.Choice_telephone())
# with session_scope() as session:
#     class_=session.query(createtables.Admin).filter().all()
# for c in class_:
#     print(c.name)
# for i in range(3001,3013):
#     Update.Class(class_id=i,major='计算机科学与技术',grade='2017',clas=i-3000)
# Update.Student(student_id=20001,telephone='1234')
